﻿/*-------------------------------------------------------------------------
* 命名空间: SiliconSelect.Common/ExcelHelper
* 类       名: ExcelHelper
* 功      能:  Excel表格的帮助类
* 时      间:  2023/8/31 12:45:49
* 版      本:  V1.0
* 作      者:  Fioman
* 格      言:  Talk is cheap,show me the code ^_^
*-------------------------------------------------------------------------*/
using DocumentFormat.OpenXml.Packaging;
using OfficeOpenXml;
using SiliconSelect.BLL;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Collections.ObjectModel;
using SiliconSelect.Models;

namespace SiliconSelect.Common
{
    public class ExcelHelper
    {
        /// <summary>
        /// 获取具体的检测规则
        /// </summary>
        /// <param name="excelPath"></param>
        /// <param name="rowCount"></param>
        /// <param name="ColCount"></param>
        /// <param name="hasTitle"></param>
        /// <returns></returns>
        public static List<List<string>> ExcelToListStringByStreamReader(string excelPath, int rowCount = 1, int ColCount = -1, bool hasTitle = false)
        {
            List<List<string>> resList = new List<List<string>>();
            try
            {
                File.SetAttributes(excelPath, FileAttributes.ReadOnly);
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                var package = new ExcelPackage(new FileInfo(excelPath));
                ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
                int rows = worksheet.Dimension.End.Row;
                int cols = worksheet.Dimension.End.Column;

                for (int i = 1; i <= rows; i++)
                {
                    List<string> rowData = new List<string>();
                    if (i == 1)
                    {
                        if (hasTitle)
                        {
                            continue;
                        }
                    }
                    for (int j = 1; j <= cols; j++)
                    {
                        var cellVal = worksheet.Cells[i, j].Value;
                        if (cellVal != null)
                        {
                            rowData.Add(cellVal.ToString()!);
                        }
                        else
                        {
                            rowData.Add(string.Empty);
                        }
                    }
                    resList.Add(rowData);
                }
                package.Dispose();
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(LogType.Fatal, ExceptionHelper.GetFullException(ex, MethodBase.GetCurrentMethod()!));
                return resList;
            }
            //string? strline;
            //Encoding encoding = Encoding.GetEncoding("GB2312");
            //int rowIndex = 1;
            //// 这段代码的作用就是如果这个文件已经被其它的进程打开了,它依然可以以只读的方式打开这个文件.
            //using (var fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            //using (StreamReader reader = new StreamReader(fileStream, encoding))
            //{
            //    while (!string.IsNullOrEmpty(strline = reader.ReadLine()))
            //    {
            //        if (rowIndex < startLine)
            //        {
            //            rowIndex++;
            //            continue;
            //        }
            //        if (rowIndex >= endLine && endLine != -1)
            //        {
            //            break;
            //        }
            //        string pattern = @",(?=(?:[^""]*""[^""]*"")*[^""]*$)";
            //        // 去除掉空字符串
            //        List<string> lineRes = Regex.Split(strline, pattern).
            //            Where(s => !string.IsNullOrEmpty(s)).ToList();
            //        resList.Add(lineRes);
            //        rowIndex++;
            //    }
            //}
            return resList;
        }


        /// <summary>
        /// 将CSV文件转换为DataTable (文件流的方式),可以指定开始行,-1表示末尾.
        /// hasTitle是否具有标题,默认是没有标题的.
        /// </summary>
        /// <param name="csvPath"></param>
        /// <param name="startLine">从第一行开始(包括第一行)</param>
        /// <param name="endLine">到哪一行结束(不包括最后一行)</param>
        /// <param name="hasTitle"></param>
        /// <returns></returns>
        public static DataTable ExcelToDataTableByStreamReader(string excelPath, int rowCount = 1, int colCount = -1, bool hasTitle = true)
        {
            File.SetAttributes(excelPath, FileAttributes.ReadOnly);
            DataTable dt = new DataTable("xlsx");
            DataRow row;
            try
            {
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                var package = new ExcelPackage(new FileInfo(excelPath));
                ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                int rows = worksheet.Dimension.End.Row;
                int cols = worksheet.Dimension.End.Column;

                for (int i = 1; i <= rows; i++)
                {
                    row = dt.NewRow();
                    for (int j = 1; j <= colCount; j++)
                    {
                        var cellVal = worksheet.Cells[i, j].Value;
                        if (cellVal != null)
                        {
                            if (i == 1)
                            {
                                dt.Columns.Add(cellVal.ToString());
                            }
                            else
                            {
                                row[j - 1] = cellVal;
                            }
                        }
                        else
                        {
                            if (i == 1)
                            {
                                dt.Columns.Add(string.Empty);
                            }
                            else
                            {
                                row[j - 1] = string.Empty;
                            }
                        }
                    }
                    if (i > 1)
                    {
                        dt.Rows.Add(row);
                    }
                }
                package.Dispose();
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(LogType.Fatal, ExceptionHelper.GetFullException(ex, MethodBase.GetCurrentMethod()!));
                return dt;
            }
            return dt;

            //    int intColCount = 0;
            //    DataColumn column;
            //    DataRow row;
            //    string? strline;
            //    string[] lineArray;
            //    Encoding encoding = Encoding.GetEncoding("GB2312");
            //    int rowIndex = 1;
            //    // 这段代码的作用就是如果这个文件已经被其它进程打开了,它依然可以以只读的方式打开这个文件.
            //    using (var fileStream = new FileStream(csvPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            //    using (StreamReader reader = new StreamReader(fileStream, encoding))
            //    {
            //        while (!string.IsNullOrEmpty(strline = reader.ReadLine()))
            //        {
            //            if (rowIndex < startLine)
            //            {
            //                rowIndex++;
            //                continue;
            //            }
            //            if (rowIndex >= endLine && endLine != -1)
            //            {
            //                break;
            //            }
            //            string pattern = @",(?=(?:[^""]*""[^""]*"")*[^""]*$)";
            //            // 去除掉空字符串
            //            lineArray = Regex.Split(strline, pattern).
            //                Where(s => !string.IsNullOrEmpty(s)).ToArray();

            //            if (hasTitle)
            //            {
            //                hasTitle = false;
            //                intColCount = lineArray.Length;
            //                for (int i = 0; i < intColCount; i++)
            //                {
            //                    column = new DataColumn(lineArray[i]);
            //                    csvDt.Columns.Add(column);
            //                }
            //                rowIndex++;
            //                continue;
            //            }

            //            row = csvDt.NewRow();
            //            for (int i = 0; i < intColCount; i++)
            //            {
            //                if (i < lineArray.Length)
            //                {
            //                    row[i] = lineArray[i];
            //                }
            //                else
            //                {
            //                    row[i] = "";
            //                }
            //            }
            //            csvDt.Rows.Add(row);
            //            rowIndex++;
            //        }
            //    }
            //    return csvDt;
            //}
        }


        /// <summary>
        /// 将Datatable的数据导入到文件当中去.
        /// </summary>
        /// <param name="FilePath"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public static DataResult SaveDatatableToExcel(string FilePath, DataTable table, uint sheetId, string sheetName, bool append)
        {
            DataResult result = new DataResult();
            try
            {
                using (SpreadsheetDocument document = SpreadsheetDocument.Open(FilePath, true))
                {
                    WorkbookPart workbookPart = document.AddWorkbookPart();
                    workbookPart.Workbook = new Workbook();

                    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                    var sheetData = new SheetData();
                    worksheetPart.Worksheet = new Worksheet(sheetData);

                    Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
                    Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = sheetId, Name = sheetName };

                    sheets.Append(sheet);

                    Row headerRow = new Row();

                    List<String> columns = new List<string>();
                    foreach (System.Data.DataColumn column in table.Columns)
                    {
                        columns.Add(column.ColumnName);

                        Cell cell = new Cell();
                        cell.DataType = CellValues.String;
                        cell.CellValue = new CellValue(column.ColumnName);
                        headerRow.AppendChild(cell);
                    }

                    sheetData.AppendChild(headerRow);

                    foreach (DataRow dsrow in table.Rows)
                    {
                        Row newRow = new Row();
                        foreach (String col in columns)
                        {
                            Cell cell = new Cell();
                            cell.DataType = CellValues.String;
                            cell.CellValue = new CellValue(dsrow[col].ToString()!);
                            newRow.AppendChild(cell);
                        }

                        sheetData.AppendChild(newRow);
                    }
                    workbookPart.Workbook.Save();
                    result.State = true;
                    return result;
                }
            }
            catch (Exception ex)
            {
                result.Message = ExceptionHelper.GetFullException(ex, MethodBase.GetCurrentMethod()!);
            }
            return result;
        }

        internal static DataResult SaveRecipeDatatableToExcel(string filePath, ObservableCollection<RecipeDectectedRuleModel> detectedRuleList, ObservableCollection<RecipeConfigModel> recipeConfigList)
        {
            DataResult result = new DataResult();
            File.SetAttributes(filePath, FileAttributes.Normal);
            try
            {
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;//指明非商业应用
                ExcelPackage package = new ExcelPackage(filePath);//加载Excel工作簿
                ExcelWorksheet sheet1 = package.Workbook.Worksheets["测试配方内容"];//读取工作簿中名为"Sheet1"的工作表


                List<string> writeStr = new List<string>();
                List<string> noWriteList = new List<string>()
                {
                    "线激光测量","电阻率测量","尺寸","前崩边","后崩边","左崩边","右崩边","上脏污","下脏污","隐裂1","隐裂2"
                };
                for (int i = 0; i < detectedRuleList.Count; i++)
                {
                    var model = detectedRuleList[i];
                    writeStr.Clear();
                    writeStr.Add(string.IsNullOrEmpty(model.DetectedName) ? "" : model.DetectedName);
                    writeStr.Add(string.IsNullOrEmpty(model.Description) ? "" : model.Description);
                    writeStr.Add(string.IsNullOrEmpty(model.Unit) ? "" : model.Unit);
                    writeStr.Add(string.IsNullOrEmpty(model.Precis) ? "" : model.Precis);
                    writeStr.Add(string.IsNullOrEmpty(model.Level01Val) ? "" : model.Level01Val);
                    writeStr.Add(string.IsNullOrEmpty(model.Level02Val) ? "" : model.Level02Val);
                    writeStr.Add(string.IsNullOrEmpty(model.Level03Val) ? "" : model.Level03Val);
                    writeStr.Add(string.IsNullOrEmpty(model.Level04Val) ? "" : model.Level04Val);
                    writeStr.Add(string.IsNullOrEmpty(model.Level05Val) ? "" : model.Level05Val);
                    writeStr.Add(string.IsNullOrEmpty(model.Level06Val) ? "" : model.Level06Val);
                    writeStr.Add(string.IsNullOrEmpty(model.Level07Val) ? "" : model.Level07Val);
                    writeStr.Add(string.IsNullOrEmpty(model.Level08Val) ? "" : model.Level08Val);
                    writeStr.Add(string.IsNullOrEmpty(model.Level09Val) ? "" : model.Level09Val);
                    writeStr.Add(string.IsNullOrEmpty(model.Level10Val) ? "" : model.Level10Val);
                    writeStr.Add(string.IsNullOrEmpty(model.Level11Val) ? "" : model.Level11Val);
                    writeStr.Add(string.IsNullOrEmpty(model.Level12Val) ? "" : model.Level12Val);
                    writeStr.Add(string.IsNullOrEmpty(model.Level13Val) ? "" : model.Level13Val);
                    writeStr.Add(string.IsNullOrEmpty(model.Level14Val) ? "" : model.Level14Val);
                    writeStr.Add(string.IsNullOrEmpty(model.Level15Val) ? "" : model.Level15Val);
                    writeStr.Add(string.IsNullOrEmpty(model.Level16Val) ? "" : model.Level16Val);
                    writeStr.Add(string.IsNullOrEmpty(model.Level17Val) ? "" : model.Level17Val);
                    writeStr.Add(string.IsNullOrEmpty(model.Level18Val) ? "" : model.Level18Val);
                    writeStr.Add(string.IsNullOrEmpty(model.Level19Val) ? "" : model.Level19Val);
                    writeStr.Add(string.IsNullOrEmpty(model.Level20Val) ? "" : model.Level20Val);
                    writeStr.Add(string.IsNullOrEmpty(model.Level21Val) ? "" : model.Level21Val);

                    if (!noWriteList.Contains(writeStr[0]))
                    {
                        // 如果不包含这些标题,就写入.写入的时候,注意是错一位的.
                        for (int j = 1; j <= writeStr.Count; j++)
                        {
                            sheet1.Cells[i + 2, j].Value = writeStr[j - 1];
                        }
                    }
                }

                // 保存Sheet2的值.
                ExcelWorksheet sheet2 = package.Workbook.Worksheets["测试配方配置"];//读取工作簿中名为"Sheet2"的工作表
                for (int i = 0; i < recipeConfigList.Count; i++)
                {
                    var model = recipeConfigList[i];
                    writeStr.Clear();
                    writeStr.Add(model.Level.ToString());
                    writeStr.Add(string.IsNullOrEmpty(model.IsUsed) ? "" : model.IsUsed);
                    writeStr.Add(string.IsNullOrEmpty(model.LevelType) ? "" : model.LevelType);
                    writeStr.Add(string.IsNullOrEmpty(model.TypeDescription) ? "" : model.TypeDescription);
                    writeStr.Add(string.IsNullOrEmpty(model.BoxesInto) ? "" : model.BoxesInto);
                    for (int j = 1; j <= writeStr.Count; j++)
                    {
                        sheet2.Cells[i + 2, j].Value = writeStr[j - 1];
                    }
                }

                package.Save();
                package.Dispose();
                result.State = true;
            }
            catch (Exception ex)
            {
                result.Message = ExceptionHelper.GetFullException(ex, MethodBase.GetCurrentMethod()!);
            }
            return result;
        }
    }
}
